#include "database.h"

Database::Database(QObject *parent) : QObject(parent)
{
    //数据库连接
        db = QSqlDatabase::addDatabase("QODBC");
        db.setHostName("127.0.0.1");
        db.setPort(3306);
        db.setDatabaseName("library_management"); //不是Database,记得别填错
        db.setUserName("root");
        db.setPassword("2003813");
        qDebug() <<"连接成功"<<endl;
}

Database::~Database()
{
    if (db.isOpen()) {
        db.close();
    }
}

bool Database::connect()
{
    if (!db.open()) {
        qDebug() << "Error: Failed to connect to database:" << db.lastError().text();
        return false;
    }
    return true;
}

bool Database::disconnect()
{
    if (db.isOpen()) {
        db.close();
        return true;
    }
    return false;
}

bool Database::isConnected() const
{
    return db.isOpen();
}



bool Database::validateUser(const QString &username, const QString &password)
{
    QSqlQuery query;
    query.prepare("SELECT id FROM users WHERE username = ? AND password = ?");
    query.addBindValue(username);
    query.addBindValue(password);

    if (!query.exec()) {
        qDebug() << "Error validating user:" << query.lastError().text();
        return false;
    }

    return query.next();
}

bool Database::addUser(const QString &username, const QString &password)
{
    QSqlQuery query;
    query.prepare("INSERT INTO users (username, password) VALUES (?, ?)");
    query.addBindValue(username);
    query.addBindValue(password);

    if (!query.exec()) {
        qDebug() << "Error adding user:" << query.lastError().text();
        return false;
    }

    return true;
}

bool Database::changePassword(const QString &username, const QString &oldPassword, const QString &newPassword)
{
    QSqlQuery query;
    query.prepare("UPDATE users SET password = ? WHERE username = ? AND password = ?");
    query.addBindValue(newPassword);
    query.addBindValue(username);
    query.addBindValue(oldPassword);

    if (!query.exec()) {
        qDebug() << "Error changing password:" << query.lastError().text();
        return false;
    }

    return query.numRowsAffected() > 0;
}

bool Database::addBook(const QString &id, const QString &title, const QString &author,
                      const QString &publisher, bool available)
{
    QSqlQuery query;
    query.prepare("INSERT INTO books (id, title, author, publisher, available) "
                 "VALUES (?, ?, ?, ?, ?)");
    query.addBindValue(id);
    query.addBindValue(title);
    query.addBindValue(author);
    query.addBindValue(publisher);
    query.addBindValue(available);

    if (!query.exec()) {
        qDebug() << "Error adding book:" << query.lastError().text();
        return false;
    }

    return true;
}

bool Database::updateBook(const QString &id, const QString &title, const QString &author,
                         const QString &publisher, bool available)
{
    QSqlQuery query;
    query.prepare("UPDATE books SET title = ?, author = ?, publisher = ?, available = ? "
                 "WHERE id = ?");
    query.addBindValue(title);
    query.addBindValue(author);
    query.addBindValue(publisher);
    query.addBindValue(available);
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Error updating book:" << query.lastError().text();
        return false;
    }

    return query.numRowsAffected() > 0;
}

bool Database::deleteBook(const QString &id)
{
    QSqlQuery query;
    query.prepare("DELETE FROM books WHERE id = ?");
    query.addBindValue(id);

    if (!query.exec()) {
        qDebug() << "Error deleting book:" << query.lastError().text();
        return false;
    }

    return query.numRowsAffected() > 0;
}

bool Database::borrowBook(const QString &bookId, const QString &userId)
{
    QSqlQuery query;
    db.transaction();

    // 检查图书是否可借
    query.prepare("SELECT available_copies FROM books WHERE id = ?");
    query.addBindValue(bookId);
    if (!query.exec() || !query.next() || query.value(0).toInt() <= 0) {
        db.rollback();
        return false;
    }

    // 更新图书状态
    query.prepare("UPDATE books SET available_copies = available_copies - 1 WHERE id = ?");
    query.addBindValue(bookId);
    if (!query.exec()) {
        db.rollback();
        return false;
    }

    // 创建借阅记录
    query.prepare("INSERT INTO borrow_records (username, book_id, due_date) VALUES (?, ?, DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 30 DAY))");
    query.addBindValue(userId);
    query.addBindValue(bookId);
    if (!query.exec()) {
        db.rollback();
        return false;
    }

    return db.commit();
}

bool Database::returnBook(const QString &bookId, const QString &userId)
{
    QSqlQuery query;
    db.transaction();

    // 更新图书状态
    query.prepare("UPDATE books SET available_copies = available_copies + 1 WHERE id = ?");
    query.addBindValue(bookId);
    if (!query.exec()) {
        db.rollback();
        return false;
    }

    // 更新借阅记录
    query.prepare("UPDATE borrow_records SET status = 'returned', return_date = CURRENT_TIMESTAMP "
                 "WHERE book_id = ? AND username = ? AND status = 'borrowed'");
    query.addBindValue(bookId);
    query.addBindValue(userId);
    if (!query.exec()) {
        db.rollback();
        return false;
    }

    return db.commit();
}

QSqlQuery Database::getBookInfo(const QString &id)
{
    QSqlQuery query;
    query.prepare("SELECT * FROM books WHERE id = ?");
    query.addBindValue(id);
    query.exec();
    return query;
}

QSqlQuery Database::searchBooks(const QString &keyword)
{
    QSqlQuery query;
    query.prepare("SELECT * FROM books WHERE id LIKE ? OR title LIKE ? OR author LIKE ? OR publisher LIKE ?");
    QString pattern = "%" + keyword + "%";
    query.addBindValue(pattern);
    query.addBindValue(pattern);
    query.addBindValue(pattern);
    query.addBindValue(pattern);
    query.exec();
    return query;
}

QSqlQuery Database::getBorrowHistory(const QString &userId)
{
    QSqlQuery query;
    query.prepare("SELECT br.*, b.title, b.author FROM borrow_records br "
                 "JOIN books b ON br.book_id = b.id "
                 "WHERE br.username = ? "
                 "ORDER BY br.borrow_date DESC");
    query.addBindValue(userId);
    query.exec();
    return query;
}

QSqlQuery Database::getCurrentBorrows(const QString &userId)
{
    QSqlQuery query;
    query.prepare("SELECT br.*, b.title, b.author FROM borrow_records br "
                 "JOIN books b ON br.book_id = b.id "
                 "WHERE br.username = ? AND br.status = 'borrowed' "
                 "ORDER BY br.borrow_date DESC");
    query.addBindValue(userId);
    query.exec();
    return query;
}
